package DBLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;

import ModelLayer.OrderLine;

import ModelLayer.Product;
import ModelLayer.SalesOrder;

public class DBOrderLine implements IFDBOrderLine {

	private Connection con;

	public DBOrderLine() {

		con = DbConnection1.getInstance().getDBcon();
	}

	@Override
	public ArrayList<OrderLine> getAllOrderLines(int salesOrderID,
			boolean retriveAssociation) {
		return miscWhere("", retriveAssociation);
	}

	private ArrayList<OrderLine> miscWhere(String wClause,
			boolean retriveAssociation) {
		ResultSet results;
		ArrayList<OrderLine> list = new ArrayList<OrderLine>();

		String query = buildQuery(wClause);

		try { // read the order line from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			while (results.next()) {

				OrderLine oLineObj = new OrderLine();
				oLineObj = buildOrderLine(results);

				// association is to be build

				if (retriveAssociation) {

					DBSalesOrder dbSales = new DBSalesOrder();
					DBProduct dbProduct = new DBProduct();
					int saleOrderID = oLineObj.getSalesOrder().getSaleID();
					SalesOrder salesOrder = dbSales.findSalesOrder(saleOrderID,
							false);
					System.out.println("Main order is seleceted");
					int productNumber = oLineObj.getProduct().getId();
					Product product = dbProduct.findProduct(productNumber,
							false);

					oLineObj.setProduct(product);
					oLineObj.setSalesOrder(salesOrder);

				}

				list.add(oLineObj);

				stmt.close();
			}
		}

		catch (Exception e) {
			System.out.println("Query exception - select: " + e);
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public OrderLine findOrderLine(int saleOrderID, int productID,
			boolean retriveAssociation) {
		String wClause = "  Sale_ID = '" + saleOrderID + "' and  Prod_ID='"
				+ productID + "'";
		return singleWhere(wClause, retriveAssociation);
	}

	private OrderLine singleWhere(String wClause, boolean retriveAssociation) {
		ResultSet results;
		OrderLine oLineOBj = new OrderLine();

		String query = buildQuery(wClause);
		System.out.println(query);
		try { // read the OrderLine from the database
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);

			if (results.next()) {
				oLineOBj = buildOrderLine(results);
				// assocaition is to be build
				stmt.close();
				if (retriveAssociation) {

					DBSalesOrder dbSales = new DBSalesOrder();
					DBProduct dbProduct = new DBProduct();
					int saleOrderID = oLineOBj.getSalesOrder().getSaleID();
					SalesOrder salesOrder = dbSales.findSalesOrder(saleOrderID,
							false);
					System.out.println("Main order is seleceted");
					int productNumber = oLineOBj.getProduct().getId();
					Product product = dbProduct.findProduct(productNumber,
							false);

					oLineOBj.setProduct(product);
					oLineOBj.setSalesOrder(salesOrder);
				}
			} else { // no OrderLine was found
				oLineOBj = null;
			}
		}// end try
		catch (Exception e) {
			System.out.println("Query exception: " + e);
		}
		return oLineOBj;
	}

	// method to build the query
	private String buildQuery(String wClause) {
		String query = "SELECT Sale_ID ,Prod_ID ,Quantity FROM OrderLine";

		if (wClause.length() > 0)
			query = query + " WHERE " + wClause;

		return query;
	}

	// method to build an order line object
	private OrderLine buildOrderLine(ResultSet results) {
		OrderLine oLineOBj = new OrderLine();
		try {

			oLineOBj.setSalesOrder(new SalesOrder(results.getInt(1)));
			oLineOBj.setProduct(new Product(results.getInt(2)));
			oLineOBj.setQuantity(results.getInt(3));

		} catch (Exception e) {
			System.out.println("error in building the order line object");
		}
		return oLineOBj;
	}

	@Override
	public int insertOrderLine(OrderLine line) throws Exception {
		int rc = -1;

		String query = "INSERT INTO OrderLine (Sale_ID, Prod_ID,Quantity) VALUES('"
				+ line.getSalesOrder().getSaleID()
				+ "', '"
				+ line.getProduct().getId() + "', " + line.getQuantity() + ")";

		System.out.println("insert : " + query);

		try {
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		} catch (SQLException ex) {
			System.out.println("problem while inserting a order line");
			throw new Exception("insertion was not correct");
		}

		return rc;
	}

	@Override
	public int updateOrderLine(OrderLine line) {

		int rc = -1;

		String query = "UPDATE customer SET " + "Quantity ='"
				+ line.getQuantity() + "'" +

				" WHERE Prod_ID = '" + line.getProduct().getId()
				+ "' and Sale_ID='" + line.getSalesOrder().getSaleID() + "'";
		System.out.println("Update query:" + query);
		try { // update OrderLine
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);

			stmt.close();
		}// end try
		catch (Exception ex) {
			System.out.println("Update exception in order line db: " + ex);
		}
		return (rc);
	}

	public int delete(OrderLine line) {
		int rc = -1;

		String query = "DELETE FROM OrderLine WHERE Sale_ID= '"
				+ line.getSalesOrder().getSaleID() + "' and Prod_ID='"
				+ line.getProduct().getId() + "'";
		System.out.println(query);
		try { // delete from OrderLine
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}// slut try
		catch (Exception ex) {
			System.out.println("Delete exception in customer db: " + ex);
		}
		return (rc);
	}

}
